#!/usr/bin/env bash
dt="`date -d "$d  0 days ago "  "+%Y-%m-%d"`"
mysql -ureadonly -preadonly123 -h192.168.0.49<<EOF
use jiayou;
select from_unixtime(created,'%Y-%m-%d')as day,use_shop_id,
sum(case when coupon_type = 1 and tactics_type = 'score' then discount_amount else 0 end) as oil_score_usedmoney,
sum(case when coupon_type = 1 and tactics_type = 'reissued' then discount_amount else 0 end ) as oil_reissued_usedmoney,
sum(case when coupon_type = 1 and tactics_type = 'order' then discount_amount else 0 end) as oil_order_usedmoney,
sum(case when coupon_type = 1 and tactics_type regexp '[0-9]{1,}' then discount_amount else 0 end) as oil_hfive_usedmoney,

sum(case when coupon_type = 3 and tactics_type = 'score' then discount_amount else 0 end) as notoil_score_usedmoney,
sum(case when coupon_type = 3 and tactics_type = 'reissued' then discount_amount else 0 end ) as notoil_reissued_usedmoney,
sum(case when coupon_type = 3 and tactics_type = 'order' then discount_amount else 0 end) as notoil_order_usedmoney,
sum(case when coupon_type = 3 and tactics_type regexp '[0-9]{1,}' then discount_amount else 0 end) as notoil_hfive_usedmoney,

sum(case when coupon_type = 2 and tactics_type = 'order' then actual_amoun else 0 end) as oil_order_usednum,
sum(case when coupon_type = 2 and tactics_type != 'order' then actual_amoun else 0 end) as oil_order_usednum

from coupon_info where coupon_statue = 3  and from_unixtime(created,'%Y-%m-%d')='$dt'
group by from_unixtime(created,'%Y-%m-%d'),use_shop_id ;

EOF